package org.example;

import org.gridgain.internal.h2.tools.DeleteDbFiles;
import org.junit.Test;

import java.sql.*;

public class MyStackData {


    public void create_table(String table_name) throws ClassNotFoundException, SQLException {
        DeleteDbFiles.execute("/Users/chenfei/temp/db", table_name, true);

        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/" + table_name);
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute("create table IF NOT EXISTS "+ table_name +"(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.close();
        conn.close();
    }

    public void create_index(String tableName, String... indexs) throws ClassNotFoundException, SQLException {

        StringBuilder sb = new StringBuilder();
        StringBuilder sb1 = new StringBuilder();
        if (indexs.length > 0)
        {

            for (int i = 0; i < indexs.length; i++)
            {
                if (i == indexs.length - 1)
                {
                    sb1.append(indexs[i]);
                }
                else
                {
                    sb1.append(indexs[i] + ",");
                }
                sb.append(indexs[i] + "_");
            }
        }
        sb.append("idx");

        String table_name = tableName + sb.toString();
        String sql = "CREATE INDEX IF NOT EXISTS "+ table_name +" ON "+ tableName +" ("+ sb1.toString() +")";
        DeleteDbFiles.execute("/Users/chenfei/temp/db", table_name, true);

        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/" + table_name);
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute(sql);

        stat.close();
        conn.close();
    }

    @Test
    public void create_table_0() throws ClassNotFoundException, SQLException {
        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/stack");
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute("create table IF NOT EXISTS stock_date(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_week(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_month(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_quarter(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_year(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_120m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_60m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_30m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_15m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_5m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("create table IF NOT EXISTS stock_1m(" +
                "    id long primary key," +
                "    st_timestamp date," +
                "    volume long," +
                "    open double," +
                "    high double," +
                "    low double," +
                "    close double," +
                "    chg double," +
                "    percent double," +
                "    turnoverrate double," +
                "    amount double," +
                "    volume_post double," +
                "    amount_post double," +
                "    pe double," +
                "    pb double," +
                "    ps double," +
                "    pcf double," +
                "    market_capital double," +
                "    balance double," +
                "    hold_volume_cn double," +
                "    hold_ratio_cn double," +
                "    net_volume_cn double," +
                "    hold_volume_hk double," +
                "    hold_ratio_hk double," +
                "    net_volume_hk  double," +
                "    symbol varchar(10))");

        stat.execute("CREATE INDEX IF NOT EXISTS stock_date_symbol_idx ON stock_date (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_week_symbol_idx ON stock_week (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_month_symbol_idx ON stock_month (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_quarter_symbol_idx ON stock_quarter (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_year_symbol_idx ON stock_year (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_120m_symbol_idx ON stock_120m (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_60m_symbol_idx ON stock_60m (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_30m_symbol_idx ON stock_30m (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_15m_symbol_idx ON stock_15m (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_5m_symbol_idx ON stock_5m (symbol)");
        stat.execute("CREATE INDEX IF NOT EXISTS stock_1m_symbol_idx ON stock_1m (symbol)");
//        ResultSet rs;
//        rs = stat.executeQuery("select * from wudafu");
//        while (rs.next()) {
//            System.out.println(rs.getString("node"));
//        }
        stat.close();
        conn.close();
    }

    @Test
    public void create_table_1() throws ClassNotFoundException, SQLException {

        DeleteDbFiles.execute("/Users/chenfei/temp/db", "stock_type", true);

        Class.forName("org.gridgain.internal.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:gg-h2:/Users/chenfei/temp/db/stock_type");
        Statement stat = conn.createStatement();

        // this line would initialize the database
        // from the SQL script file 'init.sql'
        // stat.execute("runscript from 'init.sql'");

        stat.execute("create table IF NOT EXISTS stock_type(" +
                "    id long primary key," +
                "    st_type varchar(16)," +
                "    symbol varchar(10))");

        stat.execute("CREATE INDEX IF NOT EXISTS stock_st_type_idx ON stock_type (symbol)");
//        ResultSet rs;
//        rs = stat.executeQuery("select * from wudafu");
//        while (rs.next()) {
//            System.out.println(rs.getString("node"));
//        }
        stat.close();
        conn.close();
    }
}
